﻿CREATE VIEW vantage.vw_Bridge
AS
SELECT     BRIDGE.VantageID, BRIDGE.LoanClientID, CI.LoanID, BRIDGE.ApplicationNumber, BRIDGE.CompanyRef, BRIDGE.BranchCodeRef, 
                      BRIDGE.LoanProgramRef, BRIDGE.ProductRef, BRIDGE.JurisdictionRef, BRIDGE.PortfolioRef, BRIDGE.DealerName, BRIDGE.InterestTypeRef, 
                      BRIDGE.CurrentStatusRef, BRIDGE.CurrentStatusDate, BRIDGE.CollateralStatusRef, BRIDGE.CollateralStatusDate, BRIDGE.OriginalAmountFinanced, 
                      BRIDGE.AmountFinanced, BRIDGE.OpenDate, BRIDGE.FirstDueDate, BRIDGE.LoanTerm, BRIDGE.OriginalLoanTerm, BRIDGE.OriginalInterestRate, 
                      BRIDGE.InterestRate, BRIDGE.OriginalAPR, BRIDGE.APR, BRIDGE.OriginalDueDay, BRIDGE.DueDay, BRIDGE.OriginalPaymentAmount, 
                      BRIDGE.PaymentAmount, BRIDGE.PaymentFrequencyRef, BRIDGE.OriginalFinanceCharge, BRIDGE.FinanceCharge, BRIDGE.DiscountAmount, 
                      BRIDGE.OriginalReserve, BRIDGE.OriginalWithHold, BRIDGE.PurchaseDate, BRIDGE.PurchasePrincipalPaid, BRIDGE.PurchaseInterest, 
                      BRIDGE.PurchaseInterestPaid, BRIDGE.LoanFundingDate, BRIDGE.NextDueDate, BRIDGE.MaturityDate, BRIDGE.GrossBalance, 
                      BRIDGE.InterestAccrued, BRIDGE.InterestPaid, BRIDGE.PrinicpalPaid, BRIDGE.FeePaid, BRIDGE.PastDueInterest, BRIDGE.CurrentDueInterest, 
                      BRIDGE.PastDuePrincipal, BRIDGE.CurrentDuePrincipal, BRIDGE.NSFFeeBalance, BRIDGE.LateFeeBalance, BRIDGE.LegalFeeBalance, 
                      BRIDGE.PaymentMethodFeeBalance, BRIDGE.RepoFeeBalance, BRIDGE.InsuranceFeeBalance, BRIDGE.OtherFeeBalance, 
                      BRIDGE.DefermentFeeBalance, BRIDGE.DueDayChangeFeeBalance, BRIDGE.CollectionFeeBalance, BRIDGE.RecoveryFeeBalance, BRIDGE.Make, 
                      BRIDGE.Model, BRIDGE.ModelYear, BRIDGE.VIN, BRIDGE.CashPrice, BRIDGE.MilageAtPurchase, BRIDGE.GPSTrackerID, BRIDGE.BVAtPurchase, 
                      BRIDGE.Milage, BRIDGE.MilageAsOfDate, BRIDGE.BV, BRIDGE.BVAsOfDate, BRIDGE.TitleStateRef, BRIDGE.IsRegistered, BRIDGE.IsTitleReceived, 
                      BRIDGE.RelationshipTypeRef, BRIDGE.FirstName, BRIDGE.LastName, B.FirstName + ' ' + B.LastName AS ClientName, BRIDGE.SSN, BRIDGE.DLN, 
                      BRIDGE.DOB, BRIDGE.FICO, BRIDGE.MaritalStatusRef, BRIDGE.Occupation, BRIDGE.PrimaryLanguageRef, BRIDGE.TimeZoneRef, 
                      BRIDGE.EntityStatusRef, BRIDGE.Street, BRIDGE.AptSuite, BRIDGE.City, BRIDGE.CountyRef, BRIDGE.StateRef, BRIDGE.ZipCode
FROM         (SELECT     LoanID, BorrowerID, Pos
                       FROM          dbo.t_Loan_Borrower
                       WHERE      (Pos = 0)) AS L_B INNER JOIN
                      dbo.t_Borrower AS B ON L_B.BorrowerID = B.ID INNER JOIN
                      dbo.t_CarInfo AS CI ON L_B.LoanID = CI.LoanID FULL OUTER JOIN
                      vantage.v_Bridge_10 AS BRIDGE ON B.SSN = BRIDGE.SSN AND CI.VIN = BRIDGE.VIN

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[30] 4[43] 2[8] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4[50] 3) )"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[75] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "L_B"
            Begin Extent = 
               Top = 6
               Left = 519
               Bottom = 99
               Right = 670
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "B"
            Begin Extent = 
               Top = 9
               Left = 295
               Bottom = 117
               Right = 481
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CI"
            Begin Extent = 
               Top = 169
               Left = 294
               Bottom = 277
               Right = 495
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BRIDGE"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 663
               Right = 251
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 96
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 150', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'vw_Bridge';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'0
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 2445
         Alias = 1005
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'vw_Bridge';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'vw_Bridge';

